--POUR CONTROLE


define erp='erp_mli2.';
define outils='outils_mli2.';

--CREATE TABLE se_CPT_IMPUT AS (SELECT * FROM &erp.CPT_IMPUT WHERE 1=2);




DELETE FROM se_CPT_IMPUT;

 INSERT INTO se_CPT_IMPUT
 (SELECT i.catcpta categ_cod,
         i.famcpta fam_cod,
         '01' soc_cod,
         '01' ets_cod,
         0 enreg_id,
         i.imput || '000000' cptcompte_no,
         i.sens sens
    FROM uv_cimputations i
    WHERE i.famcpta IS NOT null
  ) ;

 UPDATE se_CPT_IMPUT tf SET enreg_id=(select enreg_id FROM &erp.CPT_IMPUT f WHERE f.fam_cod=tf.fam_cod AND f.categ_cod=tf.categ_cod  ) ;
 UPDATE se_CPT_IMPUT tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;



 CALL uvse_synchroverserp('categ_cod@fam_cod@soc_cod@ets_cod','CPT_IMPUT','O') ;

 commit;



select * from (
SELECT &outils.stragg(source) source, categ_cod, fam_cod, soc_cod, ets_cod, enreg_id, cptcompte_no, sens
FROM (
  SELECT 'uv' source ,g.* FROM se_CPT_IMPUT g
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.CPT_IMPUT g
  )
GROUP BY categ_cod, fam_cod, soc_cod, ets_cod, enreg_id, cptcompte_no, sens
HAVING Count(1)!=2) r
ORDER BY categ_cod,fam_cod;